﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Odbc;
using System.Data;

namespace DotSpider.Controls
{
    public class MySQL
    {
        private OdbcConnection odbcCon;
        private OdbcCommand odbcCom;
        private OdbcDataReader odbcDR;

        public MySQL()
        {
            odbcCon = new OdbcConnection(Settings.Database.ConnectionString);
        }

        public void Connect()
        {
            try
            {
                if (odbcCon.State == ConnectionState.Closed)
                {
                    odbcCon.Open();

                    //string query = "SET NAMES utf8";
                    //odbcCom = new OdbcCommand(query, odbcCon);
                    //odbcCom.ExecuteNonQuery();

                    Settings.Logger.AddLog("DB > Connected");
                }
            }
            catch (OdbcException Ex)
            {
                throw new Exception("Error connecting to database: " + Ex.Message);
            }
        }

        public List<string[]> FetchQueue()
        {
            List<string[]> list = new List<string[]>();
            string query = "SELECT * FROM dot_queue GROUP BY address";
            odbcCom = new OdbcCommand(query, odbcCon);
            odbcDR = odbcCom.ExecuteReader();
            while (odbcDR.Read())
            {
                list.Add(new string[]
                             {
                                 odbcDR[0].ToString(),
                                 odbcDR[1].ToString(),
                                 odbcDR[2].ToString()
                             }
                    );
            }
            return list;
        }

        public void LoadAllCachedWords()
        {
            Dictionary<string, int> cachedWords = new Dictionary<string, int>();
            string query = "SELECT * FROM dot_words";
            odbcCom = new OdbcCommand(query, odbcCon);
            odbcDR = odbcCom.ExecuteReader();
            try
            {
                while (odbcDR.Read())
                {
                    if (!cachedWords.ContainsKey(odbcDR[1].ToString()))
                        cachedWords.Add(odbcDR[1].ToString(), Convert.ToInt32(odbcDR[0]));
                }
            }
            catch(Exception e)
            {
                System.Windows.Forms.MessageBox.Show(e.Message);
            }
            Settings.Words = cachedWords;
            Settings.Logger.AddLog("DB > Words Loaded");
        }

        internal int AddWord(string word)
        {
            string query = "INSERT INTO dot_words (word) VALUES ('" + word + "')";
            odbcCom = new OdbcCommand(query, odbcCon);
            odbcCom.ExecuteNonQuery();

            query = "SELECT * FROM dot_words WHERE word = '" + word + "'";
            odbcCom = new OdbcCommand(query, odbcCon);
            odbcDR = odbcCom.ExecuteReader();
            while (odbcDR.Read())
            {
                return Convert.ToInt32(odbcDR[0]);
            }
            return -1;
        }

        internal int AddPageInfo(string url, string title, int contentSize)
        {
            //Clear cache for this page
            try
            {
                string sQuery = "SELECT page_id, address FROM dot_pages WHERE address = '" + url + "'";
                odbcCom = new OdbcCommand(sQuery, odbcCon);
                odbcDR = odbcCom.ExecuteReader();
                int pageId = -1;
                while (odbcDR.Read())
                {
                    pageId = Convert.ToInt32(odbcDR[0]);
                    break;
                }
                if (pageId != -1)
                {
                    sQuery = "DELETE FROM dot_pages WHERE address = '" + url + "'";
                    odbcCom = new OdbcCommand(sQuery, odbcCon);
                    odbcCom.ExecuteNonQuery();
                    sQuery = "DELETE FROM dot_cache WHERE page_id = " + pageId;
                    odbcCom = new OdbcCommand(sQuery, odbcCon);
                    odbcCom.ExecuteNonQuery();
                }
            }
            catch(Exception e)
            {
                throw new Exception(e.Message);
            }

            //Add it again
            string query = "INSERT INTO dot_pages (address, page_caption, content_size, update_date) " +
                           "VALUES ('" + url.Replace("\'", "\\'") + "', '" + title.Replace("\'", "\\'") + "', " + contentSize + ", NOW())";
            odbcCom = new OdbcCommand(query, odbcCon);
            odbcCom.ExecuteNonQuery();

            //Get its ID
            query = "SELECT page_id, address FROM dot_pages WHERE address = '" + url + "'";
            odbcCom = new OdbcCommand(query, odbcCon);
            odbcDR = odbcCom.ExecuteReader();
            while (odbcDR.Read())
            {
                return Convert.ToInt32(odbcDR[0]);
            }
            return -1;
        }

        internal void AddPageWord(int pageId, int posinion, string word, int quantity)
        {
            int wordId = Settings.Words[word];
            string query = "INSERT IGNORE INTO dot_cache (page_id, position, word_id, quantity) " +
                           "VALUES (" + pageId + ", " + posinion + ", " + wordId + ", " + quantity + ")";
            odbcCom = new OdbcCommand(query, odbcCon);
            odbcCom.ExecuteNonQuery();
        }

        public void RemoveQueue(string url)
        {
            string sQuery = "DELETE FROM dot_queue WHERE address = '" + url + "'";
            odbcCom = new OdbcCommand(sQuery, odbcCon);
            odbcCom.ExecuteNonQuery();
        }

        public void RemoveAllQueue()
        {
            string sQuery = "DELETE FROM dot_queue";
            odbcCom = new OdbcCommand(sQuery, odbcCon);
            odbcCom.ExecuteNonQuery();
        }

        public Dictionary<KeyValuePair<string, string>, Dictionary<string, int>>
            SearchWords(Dictionary<string, int> words)
        {
            List<string> wordsList = new List<string>(words.Keys);
            Dictionary<int,string> wordsIdsDict =  new Dictionary<int, string>();
            List<string> wordsIds = new List<string>();

            //Getting words ID
            string query = "SELECT word, word_id FROM dot_words WHERE word IN ('" 
                + string.Join("','", wordsList.ToArray()) + "')";
            odbcCom = new OdbcCommand(query, odbcCon);
            odbcDR = odbcCom.ExecuteReader();
            while (odbcDR.Read())
            {
                wordsIds.Add(odbcDR[1].ToString());
                wordsIdsDict.Add(Convert.ToInt32(odbcDR[1]), odbcDR[0].ToString());
            }
            if (wordsIds.Count < 1)
            {
                return new Dictionary<KeyValuePair<string, string>, Dictionary<string, int>>();
            }

            // Get list of words, return links, ordered by 
            query = string.Format(
                "SELECT dot_pages.page_caption, dot_pages.address, "
                    +"dot_cache.quantity, dot_cache.page_id, dot_cache.word_id"
                + " FROM dot_cache, dot_pages"
                + " WHERE dot_cache.word_id IN ({0})"
                + " AND dot_pages.page_id = dot_cache.page_id"
                + " ORDER BY quantity DESC", string.Join(",", wordsIds.ToArray()));
            odbcCom = new OdbcCommand(query, odbcCon);
            odbcDR = odbcCom.ExecuteReader();

            Dictionary<KeyValuePair<string, string>, Dictionary<string, int>> table =
                new Dictionary<KeyValuePair<string, string>, Dictionary<string, int>>();
            while (odbcDR.Read())
            {
                string caption = odbcDR[0].ToString();
                string address = odbcDR[1].ToString();
                KeyValuePair<string, string> addKey = new KeyValuePair<string, string>(address, caption);
                int quantity = Convert.ToInt32(odbcDR[2]);
                int pageId = Convert.ToInt32(odbcDR[3]);
                int wordId = Convert.ToInt32(odbcDR[4]);
                // Hashtable <KeyValuePair address caption, Dictionary<word, quantity>>

                //check
                if (table.ContainsKey(addKey) && table[addKey] != null)
                {
                    if (!table[addKey].ContainsKey(wordsIdsDict[wordId]))
                        table[addKey].Add(wordsIdsDict[wordId], quantity); 
                }
                else
                {
                    Dictionary<string, int> wd = new Dictionary<string, int>();
                    wd.Add(wordsIdsDict[wordId], quantity);
                    table.Add(addKey, wd);
                }
                // address : caption / quant
            }

            return table;
        }

        public void AddToQueue(List<string> links)
        {
            List<string> addedLinks = new List<string>();
            foreach (string link in links)
            {
                if (IsPageCached(link) || addedLinks.Contains(link)) continue;

                string query = "INSERT IGNORE INTO dot_queue (address, status) " +
                           "VALUES ('" + link + "', 'added')";
                odbcCom = new OdbcCommand(query, odbcCon);
                odbcCom.ExecuteNonQuery();
                addedLinks.Add(link);
            }
        }

        public bool IsPageCached(string url)
        {
            string query = "SELECT COUNT(page_id) FROM dot_pages WHERE address = '" + url + "'";
            odbcCom = new OdbcCommand(query, odbcCon);
            odbcDR = odbcCom.ExecuteReader();
            while (odbcDR.Read())
            {
                return (Convert.ToInt32(odbcDR[0]) > 0);
            }
            return false;
        }
    }
}
